package lex.dm.utils;

import java.io.File;
import java.sql.*;
import java.util.*;

import org.lex.io.IO;

public class DBUtils
{
	private static final String USER = "user";
	private static final String PASSWORD = "pass";
	public Statement stmt = null;
	private DatabaseMetaData dbmd = null;
	static
	{
		printDrivers();
	}

	public static Connection connect() throws SQLException
	{
		return connect( Prefs.getDatabaseDirectory(), Prefs.getCatalogName() );
	}

	public static Connection connect( File path, String name ) throws SQLException
	{
		// jdbc:derby:data/db/helloDB;create=true
		// url
		StringBuilder url = new StringBuilder( "jdbc:derby:" );
		url.append( IO.getCanonicalPath( path ) );
		url.append( ";create=true" );
		// properties
		Properties props = new Properties();
		props.put( "user", USER );
		props.put( "password", PASSWORD );
		return DriverManager.getConnection( url.toString(), props );
	}

	/**
	 * get dbms's meta-data
	 */
	public static Map<String, Object> getMetaData( Connection con )
	{
		Map<String, Object> md = new HashMap<String, Object>();
		try
		{
			DatabaseMetaData dbmd = con.getMetaData();
			md.put( "Product Name", dbmd.getDatabaseProductName() );
			md.put( "Product Version", dbmd.getDatabaseProductVersion() );
			md.put( "Driver Name", dbmd.getDriverName() );
			md.put( "Driver Version", dbmd.getDriverVersion() );
			md.put( "JDBC Version", dbmd.getJDBCMajorVersion() + "." + dbmd.getJDBCMinorVersion() );
			md.put( "Catalog Term", dbmd.getCatalogTerm() );
			md.put( "Catalog Separator", dbmd.getCatalogSeparator() );
			md.put( "Supports SQL92 Entry Level SQL", dbmd.supportsANSI92EntryLevelSQL() ? "Yes"
					: "No" );
			md.put( "Supports SQL92 Intermediate SQL", dbmd.supportsANSI92IntermediateSQL() ? "Yes"
					: "No" );
			md.put( "Supports SQL92 Full SQL", dbmd.supportsANSI92FullSQL() ? "Yes" : "No" );
			md.put( "Supports Transactions", dbmd.supportsTransactions() ? "Yes" : "No" );
			md.put( "Supports ALTER TABLE with Add Column",
				dbmd.supportsAlterTableWithAddColumn() ? "Yes" : "No" );
			md.put( "Supports ALTER TABLE with Drop Column", dbmd
				.supportsAlterTableWithDropColumn() ? "Yes" : "No" );
			md.put( "Supports Convert", dbmd.supportsConvert() ? "Yes" : "No" );
			md.put( "Max. Connection", String.valueOf( dbmd.getMaxConnections() ) );
			md.put( "Max. Statement Length", String.valueOf( dbmd.getMaxStatementLength() ) );
			md.put( "Max. Table Name Length", String.valueOf( dbmd.getMaxTableNameLength() ) );
			md.put( "Max. Column Name Length", String.valueOf( dbmd.getMaxColumnNameLength() ) );
			md.put( "Max. Column In Table", String.valueOf( dbmd.getMaxColumnsInTable() ) );
			md.put( "Max. Row Size", dbmd.getMaxRowSize() + " bytes" );
			return md;
		}
		catch( SQLException e )
		{
			e.printStackTrace();
			return md;
		}
	}

	public static Map<String, String> getDBMSDetail( Connection con ) throws SQLException
	{
		Map<String, String> list = new HashMap<String, String>();
		DatabaseMetaData dbmd = con.getMetaData();
		list.put( "Key Words", dbmd.getSQLKeywords() );
		list.put( "Numeric Functions", dbmd.getNumericFunctions() );
		list.put( "String Functions", dbmd.getStringFunctions() );
		list.put( "System Functions", dbmd.getSystemFunctions() );
		list.put( "Time&Date Functions", dbmd.getTimeDateFunctions() );
		return list;
	}

	/**
	 * get all catalogs
	 */
	public static List<String> getCatalogs( Connection con ) throws SQLException
	{
		List<String> catalogs = new ArrayList<String>();
		ResultSet rs = con.getMetaData().getCatalogs();
		while( rs.next() )
			catalogs.add( rs.getString( 1 ) );
		return catalogs;
	}

	/**
	 * ��catalogָ����database��ȡ�����б������
	 */
	public static List<String> getTableNames( Connection con, String catalog ) throws SQLException
	{
		List<String> list = new ArrayList<String>();
		ResultSet rs = con.getMetaData().getTables( catalog, null, null, new String[]{ "TABLE" } );
		while( rs.next() )
			list.add( rs.getString( "TABLE_NAME" ) );
		return list;
	}

	public static List<FieldInfo> getFields( Connection con, String catalog, String table )
		throws SQLException
	{
		List<FieldInfo> list = new ArrayList<FieldInfo>();
		ResultSet rs = con.getMetaData().getColumns( catalog, null, table, null );
		while( rs.next() )
			list.add( new FieldInfo( rs.getString( "COLUMN_NAME" ), rs.getInt( "DATA_TYPE" ), rs
				.getString( "TYPE_NAME" ) ) );
		return list;
	}

	/*
	 * ȡ��ָ���ı�Ľṹ,field info
	 */
	private List getObjectList( ResultSet rs, List field ) throws SQLException
	{
		this.prepareList( field );
		return field;
	}

	// -------------------------------------------------
	private List prepareList( List list )
	{
		if( null == list )
			list = new ArrayList();
		else
			list.clear();
		return list;
	}

	/*
	 * ȡ��Result Set�������ֶε���
	 */
	public List getFieldNames( ResultSet rs, List list ) throws SQLException
	{
		this.prepareList( list );
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		for( int i = 1; i <= columnCount; i++ )
			list.add( rsmd.getColumnName( i ) );
		return list;
	}

	/*
	 * ȡ�ñ�����ݣ�������ݺ��ֶ���
	 */
	public List getTableContents( ResultSet rs, List contents ) throws SQLException
	{
		if( false == rs.isBeforeFirst() )
			rs.beforeFirst();
		this.prepareList( contents );
		ArrayList row = null;
		int columnCount = rs.getMetaData().getColumnCount(), i = 1;
		while( rs.next() )
		{
			row = new ArrayList( columnCount );
			for( i = 1; i <= columnCount; i++ )
				row.add( rs.getString( i ) );
			contents.add( row );
		}
		return contents;
	}

	private List getPrimaryKeys( String catalog, String table, List primaryKeys )
		throws SQLException
	{
		this.prepareList( primaryKeys );
		ResultSet rs = this.dbmd.getPrimaryKeys( catalog, null, table );
		while( rs.next() )
			primaryKeys.add( rs.getString( "COLUMN_NAME" ) );
		return primaryKeys;
	}

	public List getTableFields( String catalog, String table, List field, List primaryKeys )
		throws SQLException
	{
		return this.getObjectList( this.stmt.executeQuery( "select * " + "from " + catalog + "."
				+ table + " where 1=2 " ), field );
	}

	public List getTableContents( String catalog, String table, List contents, List fieldInfos,
		List fieldNames, List primaryKeys ) throws SQLException
	{
		ResultSet rs = stmt.executeQuery( "select * from " + catalog + "." + table );
		this.getTableContents( rs, contents );
		this.getObjectList( rs, fieldInfos );
		this.getPrimaryKeys( catalog, table, primaryKeys );
		return this.getFieldNames( rs, fieldNames );
	}

	public List getDBMSTypeNames()
	{
		ArrayList list = new ArrayList();
		try
		{
			ResultSet rs = dbmd.getTypeInfo();
			while( rs.next() )
				list.add( rs.getString( "TYPE_NAME" ) );
			Collections.sort( list );
			return list;
		}
		catch( SQLException e )
		{
			e.printStackTrace();
			Collections.sort( list );
			return list;
		}
	}

	private static void printDrivers()
	{
		System.out.println( "Aviable Drivers:" );
		StringBuilder buf = new StringBuilder();
		Enumeration<Driver> drivers = DriverManager.getDrivers();
		while( drivers.hasMoreElements() )
		{
			Driver d = drivers.nextElement();
			buf.setLength( 0 );
			buf.append( d.getClass().getName() ).append( ' ' );
			buf.append( d.getMajorVersion() ).append( '.' );
			buf.append( d.getMinorVersion() );
			System.out.println( buf.toString() );
		}
	}

	private DBUtils()
	{}
}
